<?php

namespace app\admin\controller;

use cmf\controller\AdminBaseController;
use think\Controller;
use think\Request;
use think\Db;
use think\db\Query;

class StudentController extends AdminBaseController
{
    //显示
    public function index()
    {
        //搜索条件
        $name=$this->request->param('name');
        //接收school_id的值
        $school_id = $this->request->param('school_id');
        //接收room_id的值
        $room_id = $this->request->param('room_id');
        //查询学生列表中school_id的值
        $room = Db::name('room') ->where('school_id', $school_id) ->select();  
        //查询学校列表的数据         
        $school=Db::name('school') ->select();
        //连接数据库可以去掉表前缀，此方法可自己读取数据库配置文件中的表前缀
        $student=Db::name('student')
            //表连接
            ->join('cmf_room','cmf_student.room_id=cmf_room.id','LEFT')
            ->join('cmf_school','cmf_room.school_id=cmf_school.id','LEFT')
            ->join('cmf_record','cmf_record.student_id=cmf_student.id','LEFT')
            ->join('cmf_score','cmf_score.id=cmf_record.score_id','LEFT')
            //查询数据
            ->field('cmf_student.*,cmf_school.name as school_name,cmf_room.name as room_name,cmf_school.id as school_id')
            //求和score字段
            ->field('sum(cmf_score.name) as total')
            //根据每个学生id分组
            ->group('cmf_student.id')
            //接收所查询的值,使用where的闭包函数查询
            ->where(function(Query $query) use ($name,$school_id,$room_id){
                //学生姓名搜索
                if($name){
                    $query->where('cmf_student.name','like',"%$name%");
                }
                //学校名称搜索
                if ($school_id) {
                    $query->where('cmf_school.id', 'eq', $school_id );
                }
                //班级名称搜索
                if ($room_id) {
                    $query->where('cmf_room.id', 'eq', $room_id);
                }
            })      
            //倒序排列（ASC为正序）      
            ->order("id DESC")
            //分页：每页条数，跳转路径，额外参数
            ->paginate(3, false, ['query' => request()->param()]);
        //渲染分页
        $page=$student->render();
        //渲染页面，传参
        return view('index',[
            'student'=>$student,
            'page'=>$page,
            'school_id'=>$school_id,
            'room_id'=>$room_id,
            'school'=>$school,
            'room'=>$room]);
    }

    //获取当前学校下的所有班级
    public function getRoom()
    {
        //获取当前学校id
        $schoolId = input('school_id');
        //查询此学校下的所有班级
        $room = Db::name('room')->where('school_id',$schoolId)->select();
        //判断此学校下是否有班级
        if ($room){
            //定义一个空数组
            $options = ["<option value=''>-请选择班级-</option>"];
            //遍历输出
            foreach ($room as $v){
                //选中学校后对应的班级值
                $opt = '<option class = "form-control" value="'.$v['id'].'">'.$v['name'].'</option>';
                //使用array_push函数向第一个参数的数组尾部添加一个或多个元素，然后返回新数组的长度（填充数组）
                array_push($options, $opt);
            }
            //返回输出
            return $options;
        }else{
            return "<option value=''>暂无数据</option>";
        }
    }

    //添加
    public function add(Request $request)
    {
        //判断提交类型
        if (request()->isPost()){
            //获取提交数据
            $data = input();
            //判断学生姓名是否为空
            if (empty($data['name'])) {
                $this->error('姓名不能为空');
            }
            //使用函数方法获取新的时间
            $data['create_time'] = date('Y-m-d H:i:s',time());
            //删除图片变量
            unset($data['photo_name']);
            //获取数据库中的id，添加数据
            $add = Db::name('student')->insert($data);
            //判断是否添加成功
            if ($add){
                $this->success('添加成功','student/index');
            }else{
                $this->error('添加失败');
            }
        }
        //查询学校列表中的信息
        $school = Db::name('school')->select();
        //渲染页面，传参
        return view('student_add',['school' => $school]);
    }

    //编辑
    public function edit()
    {
        //判断提交类型
        if (request()->isPost()) {
            //获取当前id对应的全部数据
            $data = input();
            //使用函数方法获取新的时间
            $data['upload_time']=date('Y-m-d H:i:s',time());
            //删除图片变量
            unset($data['photo_name']);
            //获取数据库中的id，修改数据
            $edit = Db::name('student')->update($data, ['id' => $data['id']]);
            //判断是否修改成功
            if ($edit) {
                $this->success('修改成功','student/index');
            } else {
                $this->error('修改失败');
            }
        }
        //获取当前id
        $id = input('id');
        //获取要编辑的学生id
        $student = Db::name('student')->get($id);
        //获取学校列表中的所有信息
        $school=Db::name('student')->select();
        //查询班级列表中的school_id属性，获取数据
        $schoolId=Db::name('room')->where('id',$student['room_id'])->value('school_id');
        //查询班级列表中的school_id
        $room = Db::name('room')->where('school_id',$schoolId)->select();
        //渲染页面，传参
        return $this->fetch('student_edit', [
            'student'=> $student ,
            'school_id' => $schoolId,
            'room' => $room,
            'school' => $school]);
    }
    
    //删除
    public function delete()
    {
        //获取当前id对应的全部数据
        $data=input();
        //获取数据库中的id，删除数据
        $del=Db::name('student')->delete($data['id']);
        //判断是否删除成功
        if($del){
            $this->success('删除成功','student/index');
        }else{
            $this->error('删除失败');
        }
    }

    //多选删除
    public function delAll()
    {
        $ids = input('ids/a');
        $del =  Db::name('student')->delete($ids);
        if($del){
            $this->success('删除成功','student/index');
        }else{
            $this->error('删除失败');
        }
    }

    //获取成绩
    public function grade($id)
    {
        $grade = Db::name('grade')
            ->join('cmf_student student', 'cmf_grade.student_id = student.id', 'LEFT')
            ->join('cmf_room room', 'student.room_id = room.id', 'LEFT')
            ->join('cmf_subject subject', 'cmf_grade.subject_id = subject.id', 'LEFT')
            ->where('student.id',$id)
            ->field('cmf_grade.*')
            ->field('student.name as name, room.name as room_name, subject.name as subject_name, cmf_grade.name as grade_name')
            ->order('id DESC')
            ->paginate(5, false, $this->request->param());
        $page = $grade->render();
        return view('grade', ['grade' => $grade, 'page' => $page]);
    }
}